package com.ruoyi.project.qyxy.person.mapper;

import com.ruoyi.project.qyxy.person.domain.ZcPerson;
import com.ruoyi.project.qyxy.person.domain.ZzPerson;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface PersonMapper {

    /**
     * 查询职称人员
     * @param orgCode
     * @return
     */
    @Select("SELECT a.ID_NUM as idNum,a.PNAME,a.ORG_CODE as orgCode,b.CERT_SPTY as certSpty,b.CERT_RANK as certRank from T_PERSON_INFO a , " +
            "T_TECH_TITLE_INFO b WHERE a.ID_NUM = b.ID_NUM and a.ORG_CODE = #{orgCode}")
    List<ZcPerson> getZcPersonList(@Param("orgCode") String orgCode);

    /**
     * 查询注册人员
     * @param orgCode
     * @return
     */
    @Select("select idNum,PNAME,orgCode,regType,wm_concat(regspty) as regSpty,regTypeId from (\n" +
            "select a.person_name as pname,a.id_card18 as idNum,b.org_code as orgCode ,\n" +
            "decode(c.specialty_typ_num,81,'一级注册建造师',82,'二级注册建造师') as regType,c.zczy as regSpty,c.specialty_typ_num as regTypeId  \n" +
            "from U_REG_PERSON_REGINFO a,T_ENTERPRISE_INFO b,U_REG_PERSON_ZCZY c\n" +
            "where a.corp_code = b.bizlicen_num\n" +
            "and a.person_num = c.person_num\n" +
            "and b.org_code = #{orgCode}\n" +
            ") a\n" +
            "group by pname,idNum,orgCode,regType,regTypeId")
    List<ZzPerson> getZzPersonList(@Param("orgCode") String orgCode);


    @Select("select ID_CARD18 as idNum, PERSON_NAME as PNAME, CORP_CODE as orgCode, SPECIALTY_TYP_NUM as regType, SPECIALTY_TYP_NUM as regTypeId from U_REG_PERSON_REGINFO where specialty_typ_num = #{regTypeId} and CORP_CODE = #{orgCode}")
    List<ZzPerson> getJlPersonList(@Param("orgCode") String orgCode, @Param("regTypeId") String regTypeId);
}
